﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;
using System.Data;

namespace Himp.TaskScheduling
{
    /// <summary>
    /// 并样完成后O码转制样码任务
    /// </summary>
    public class SampleCodeGenerateWorker : IHostedService
    {
        #region SQL

        public const string GET_QRCODE_SQL = @"
            SELECT [一次编号]
            FROM [dbo].[收样明细表]
            WHERE 时间 = FORMAT(GETDATE(), 'yyyy-MM-dd')
            ";

        public const string EXIST_FLAG_SQL = @"
            SELECT CASE WHEN 
            EXISTS (SELECT 1 FROM [TaskScheduling].[TS_SamplePreparationTasks] WHERE Id = @Id) 
            THEN 1 
            ELSE 0 
            END";

        public const string INSERT_SAMPLE_PREPARATION = @"
            INSERT INTO
            [TaskScheduling].[TS_SamplePreparationTasks]
            (
            Id,
            SampleCode,
            TaskAssgnTime,
            LastOperTime,
            ExecStas
            )
            VALUES
            (
            @Id,
            @SampleCode,
            GETDATE(),
            GETDATE(),
            @ExecStas
            )
            ";

        #endregion

        #region 字段

        private readonly ConnectionStringOption _connectionStrings;

        #endregion

        #region 方法

        public SampleCodeGenerateWorker(IOptions<ConnectionStringOption> connectionStrings)
        {
            _connectionStrings = connectionStrings.Value;
        }

        public Task StartAsync(CancellationToken cancellationToken)
        {
            Init();
            return Task.CompletedTask;
        }

        public Task StopAsync(CancellationToken cancellationToken)
        {
            return Task.CompletedTask;
        }

        public async void Init()
        {
            // 从源数据库读取数据
            IEnumerable<string> data = ReadQrCode();
            //await Console.Out.WriteLineAsync($"获取数据{data.Count()}");
            //List<string> data = new List<string>() { "123", "234" };
            // 将数据写入目标数据库
            await QrCodeToSampleCode(data);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IEnumerable<string> ReadQrCode()
        {
            try
            {
                string connectionString = "Data Source=10.180.4.1,2544;Initial Catalog=CoalManager_2013;User Id=admin;Password=admin@20200601;TrustServerCertificate=True";

                using (IDbConnection connection = new SqlConnection(_connectionStrings.CoalManager))
                {
                    connection.Open();
                    // 读取数据
                    var qrCodeList = connection.Query<string>(GET_QRCODE_SQL);

                    return qrCodeList;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("异常");
                Console.WriteLine(e.Message);
            }

            return Enumerable.Empty<string>();

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task QrCodeToSampleCode(IEnumerable<string> data)
        {
            string connectionString = "Server=192.168.193.2,1433;Database=Auseft_RL_Web;User Id=sa;Password=1q2w3E**;TrustServerCertificate=True";

            using (IDbConnection connection = new SqlConnection(_connectionStrings.RL_Web))
            {
                connection.Open();

                foreach (var qrCode in data)
                {
                    await Console.Out.WriteLineAsync(qrCode);
                    // 检查 ID 是否已存在
                    bool exists = await connection.ExecuteScalarAsync<bool>(EXIST_FLAG_SQL,
                        new { Id = qrCode });

                    if (!exists)
                    {
                        var codeStas = await connection.ExecuteAsync(INSERT_SAMPLE_PREPARATION,
                            new
                            {
                                Id = qrCode,
                                SampleCode = qrCode,
                                ExecStas = "就绪"
                            });
                        await Console.Out.WriteLineAsync($"{qrCode}:{codeStas}");
                    }
                    else
                    {
                        await Console.Out.WriteLineAsync($"{qrCode}:已添加");
                    }
                }
            }
        }

        #endregion
    }
}
